﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Workbench.Models;
using Workbench.Shared;

namespace Workbench.Persistance
{
    public class POSTaggerRepository : RepositoryBase
    {
        public POSTaggerRepository(Func<SQLiteConnection> connectionProvider) : base(connectionProvider) { }
          
        public void AddPOSTypeForWords(POSTypeModel POSType, IEnumerable<WordModel> words)
        {
            var con = connectionProvider();

            using (var transaction = con.BeginTransaction())
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText = "INSERT INTO Words_POSTypes (WordId, POSTypeId) VALUES (@WordId, @POSTypeId)";

                    var pWordId = new SQLiteParameter("@WordId");
                    var pPOSTypeId = new SQLiteParameter("@POSTypeId");
                    cmd.Parameters.Add(pWordId);
                    cmd.Parameters.Add(pPOSTypeId);

                    foreach (var word in words)
                    {
                        if (!word.POSTypes.Contains(POSType))
                        {
                            pWordId.Value = word.RowId;
                            pPOSTypeId.Value = POSType.RowId;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                transaction.Commit();
            }
            foreach (var word in words)
            {
                if (!word.POSTypes.Contains(POSType))
                {
                    word.POSTypes.Add(POSType);
                }
            }
        }

        public void AddPOSTypeForWords(long posTypeId, long uniqueWordId)
        {
            var con = connectionProvider();

            using (var transaction = con.BeginTransaction())
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText =
@"INSERT INTO Words_POSTypes
SELECT w.RowId AS WordId, @posTypeId AS POSTypeId
FROM Words w
WHERE UniqueWordId = @uniqueWordId;";
                    cmd.Parameters.AddWithValue("@posTypeId", posTypeId);
                    cmd.Parameters.AddWithValue("@uniqueWordId", uniqueWordId);
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

        public void AddPOSTypeForWords(long posTypeId, long uniqueWordId, long fileId)
        {
            var con = connectionProvider();

            using (var transaction = con.BeginTransaction())
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText =
@"INSERT INTO Words_POSTypes
SELECT w.RowId AS WordId, @posTypeId AS POSTypeId
FROM Words w
INNER JOIN Sentences s ON s.RowId = w.SentenceId
INNER JOIN Paragraphs p ON p.RowId = s.ParagraphId
WHERE UniqueWordId = @uniqueWordId
AND FileId = @fileId
AND NOT EXISTS(SELECT * FROM Words_POSTypes WHERE WordId = w.RowId AND POSTypeId = @posTypeId);";
                    cmd.Parameters.AddWithValue("@posTypeId", posTypeId);
                    cmd.Parameters.AddWithValue("@uniqueWordId", uniqueWordId);
                    cmd.Parameters.AddWithValue("@fileId", fileId);
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

        public void RemovePOSTypeFromWords(POSTypeModel POSType, IEnumerable<WordModel> words)
        {
            var con = connectionProvider();

            using (var transaction = con.BeginTransaction())
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText = "DELETE FROM Words_POSTypes WHERE WordId = @WordId AND POSTypeId = @POSTypeId";

                    var pWordId = new SQLiteParameter("@WordId");
                    var pPOSTypeId = new SQLiteParameter("@POSTypeId");
                    cmd.Parameters.Add(pWordId);
                    cmd.Parameters.Add(pPOSTypeId);

                    foreach (var word in words)
                    {
                        if (word.POSTypes.Contains(POSType))
                        {
                            pWordId.Value = word.RowId;
                            pPOSTypeId.Value = POSType.RowId;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                transaction.Commit();
            }
            foreach (var word in words)
            {
                if (word.POSTypes.Contains(POSType))
                {
                    word.POSTypes.Remove(POSType);
                }
            }
        }

        public void RemovePOSTypeForWords(long posTypeId, long uniqueWordId, long fileId)
        {
            var con = connectionProvider();

            using (var transaction = con.BeginTransaction())
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText = 
@"DELETE FROM Words_POSTypes 
WHERE 
POSTypeId = @posTypeId
AND WordId IN (
    SELECT w.RowId 
    FROM Words w
    INNER JOIN Sentences s ON s.RowId = w.SentenceId
    INNER JOIN Paragraphs p ON p.RowId = s.ParagraphId
    WHERE UniqueWordId = @uniqueWordId
    AND FileId = @fileId
);";
                    cmd.Parameters.AddWithValue("@uniqueWordId", uniqueWordId);
                    cmd.Parameters.AddWithValue("@posTypeId", posTypeId);
                    cmd.Parameters.AddWithValue("@fileId", fileId);
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

        public void RemovePOSTypeForWords(long posTypeId, long uniqueWordId)
        {
            var con = connectionProvider();

            using (var transaction = con.BeginTransaction())
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText = @"DELETE FROM Words_POSTypes WHERE POSTypeId = @posTypeId AND WordId IN (SELECT w.RowId FROM Words w WHERE w.UniqueWordId = @uniqueWordId);";
                    cmd.Parameters.AddWithValue("@uniqueWordId", uniqueWordId);
                    cmd.Parameters.AddWithValue("@posTypeId", posTypeId);
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

        public List<POSTypeModel> GetPOSTypes(WordModel wordModel)
        {
            var result = new List<POSTypeModel>();
            var @params = new Dictionary<string, object>() { {"@WordId", wordModel.RowId} };
            using (var reader = DbUtility.ExecuteReader(connectionProvider(),
@"SELECT RowId, Name, ParentPOSTypeId FROM Words_POSTypes WHERE WordId = @WordId;", @params))
            {
                while (reader.Read())
                {
                    var model = new POSTypeModel();
                    model.RowId = reader.GetInt64(0);
                    if (!reader.IsDBNull(1)) model.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) model.ParentPOSTypeId = reader.GetInt64(2);
                    result.Add(model);
                }
            }
            return result;
        } 
    }
}
